﻿using Light.Utility;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Light.Framework
{
    class SQLBuilder
    {
        /// <summary>
        /// 拼接一般类型（含外键关联）新增SQL
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="tableName"></param>
        /// <param name="listParam"></param>
        /// <param name="idx"></param>
        /// <returns></returns>
        public static string BuildInsertSql<T>(T obj, string tableName, List<IDataParameter> listParam, int idx)
        {
            StringBuilder builderSql = new StringBuilder();
            StringBuilder builderValues = new StringBuilder();

            List<ColumnInfo> columns = DbMapper.GetColumns<T>();
            Dictionary<string, object> belongsKeyValues = DbMapper.GetBelongsKeyValue<T>(obj);

            //拼接SQL语句  INSERT INTO tableName(colum1,column2,......)
            builderSql.Append(" INSERT INTO " + tableName);
            builderSql.Append("(");

            //一般类型列
            foreach (ColumnInfo field in columns)
            {
                if (field.ReadOnly || field.IsAutoIncrement)
                    continue;

                builderSql.Append(field.Name + ",");

                string paramName = "@" + field.Name + idx;
                builderValues.Append(paramName + ",");

                DataParameter param = GetDbDataParameter(obj, field, paramName);

                listParam.Add(param);
            }

            //外键关联列
            foreach (var columnName in belongsKeyValues.Keys)
            {
                builderSql.Append(columnName + ",");

                string paramName = "@" + columnName + idx;
                builderValues.Append(paramName + ",");

                DataParameter param = new DataParameter();
                param.ParameterName = paramName;
                param.DbType = DbType.String; //外键数据类型
                param.Value = belongsKeyValues[columnName];

                listParam.Add(param);
            }

            builderSql.Remove(builderSql.Length - 1, 1);
            builderSql.Append(")");

            //拼Values： VALUES(@p1,@p2,......);
            builderSql.Append("VALUES");
            builderSql.Append("(");
            builderSql.Append(builderValues.Remove(builderValues.Length - 1, 1).ToString());
            builderSql.Append(");");

            return builderSql.ToString();
        }

        /// <summary>
        /// 拼接删除SQL
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="primaryKey"></param>
        /// <param name="listParam"></param>
        /// <param name="id"></param>
        /// <param name="idx"></param>
        /// <returns></returns>
        public static string BuildDeleteSql(string tableName, ColumnInfo primaryKey, List<IDataParameter> listParam, object id, int idx)
        {
            string paramName = "@" + primaryKey.Name + idx.ToString();
            listParam.Add(new DataParameter(paramName, id));

            return " DELETE FROM " + tableName + " WHERE " + primaryKey.Name + "=" + paramName + ";";
        }

        /// <summary>
        /// 拼接更新SQL
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="listParam"></param>
        /// <param name="tableName"></param>
        /// <param name="primaryKey"></param>
        /// <param name="idx"></param>
        /// <returns></returns>
        public static string BuildUpdateSql<T>(T obj, List<IDataParameter> listParam, string tableName, ColumnInfo primaryKey, int idx, UpdateColumn saveColumn)
        {
            StringBuilder builderSql = new StringBuilder();

            //获取系统类型列
            List<ColumnInfo> columns = DbMapper.GetColumns<T>();

            //拼接SQL语句  UPDATE tableName SET colum1=@column1,column2=@column2,...... WHERE ID=@ID
            builderSql.Append(" UPDATE " + tableName + " SET ");

            foreach (ColumnInfo field in columns)
            {
                if (field.ReadOnly || !saveColumn.Include(field.PropertyName))
                    continue;

                string paramName = "@" + field.Name + idx;
                builderSql.Append(field.Name + "=" + paramName);
                builderSql.Append(",");

                DataParameter param = GetDbDataParameter(obj, field, paramName);
                listParam.Add(param);
            }

            //获取外键关联列 <列名，值>
            Dictionary<string, object> belongsKeyValues = DbMapper.GetBelongsKeyValue<T>(obj, saveColumn);
            foreach (var columnName in belongsKeyValues.Keys)
            {
                string paramName = "@" + columnName + idx;
                builderSql.Append(columnName + "=" + paramName);
                builderSql.Append(",");

                IDataParameter param = new DataParameter();
                param.ParameterName = paramName;
                param.DbType = DbType.String; //外键数据类型
                param.Value = belongsKeyValues[columnName];

                listParam.Add(param);
            }

            builderSql.Remove(builderSql.Length - 1, 1);

            //拼WHERE： VALUES(@p1,@p2,......);
            builderSql.Append(" WHERE " + primaryKey.Name + "=@" + primaryKey.Name + idx);

            return builderSql.ToString();
        }

        /// <summary>
        /// 拼接多对多关联表保存SQL
        /// </summary>
        /// <param name="obj">对象</param>
        /// <param name="primaryKey">主键信息</param>
        /// <param name="action">操作类型</param>
        /// <param name="updateColumn">需要更新的列</param>
        /// <returns></returns>
        public static string BuildManyToManySql<T>(T obj, ColumnInfo primaryKey, ManyToManyAction action, UpdateColumn updateColumn = null)
        {
            StringBuilder builder = new StringBuilder();

            Dictionary<string, ManyToManyInfo> dicManyToMany = DbMapper.GetManyToMany(typeof(T));
            if (dicManyToMany != null && dicManyToMany.Count > 0)
            {
                foreach (var key in dicManyToMany.Keys)
                {
                    ManyToManyInfo many = dicManyToMany[key];

                    if (updateColumn != null && !updateColumn.Include(key))
                        continue;

                    object value = ReflectionHelper.GetPropertyValue<T>(obj, primaryKey.PropertyName);

                    if (action != ManyToManyAction.Create) //修改或删除时，先清除已有关联
                    {
                        builder.Append(" DELETE FROM " + many.Table + " WHERE " + many.ColumnKey + "='" + value + "'; ");
                    }

                    if (action != ManyToManyAction.Delete) //新增或修改
                    {
                        IList objsRef = (IList)ReflectionHelper.GetPropertyValue<T>(obj, key);

                        foreach (var objRef in objsRef)
                        {
                            object objRefID = ReflectionHelper.GetPropertyValue(objRef, primaryKey.PropertyName);

                            builder.Append(string.Format(" INSERT INTO {0}(ID,{1},{2}) VALUES('{3}','{4}','{5}');",
                                many.Table, many.ColumnKey, many.ColumnRef, Guid.NewGuid().ToString("N"), value, objRefID));
                        }
                    }
                }
            }

            return builder.ToString();
        }

        /// <summary>
        /// 拼接结果列
        /// </summary>
        /// <param name="findOne">是否只取一条</param>
        /// <param name="resultColumn">需要查询的结果属性集合</param>
        /// <returns></returns>
        public static string BuildSelectSql<T>(bool findOne, ResultColumn resultColumn)
        {
            string sql = string.Empty;
            string top = findOne ? " TOP(1) " : string.Empty;

            StringBuilder builderSelect = new StringBuilder();
            builderSelect.Append(top);

            if (resultColumn == null)
                resultColumn = new ResultColumn();

            Dictionary<string, BelongsInfo> dicBelongs = DbMapper.GetBelongsColumnsDic<T>();

            if (dicBelongs != null && dicBelongs.Count > 0)
            {

                #region 有外键关联的情况

                List<ColumnInfo> tableColumns = DbMapper.GetColumns(typeof(T), 0);
                string tableNameAlias = DbMapper.PRIMARY_TABLE_ALIAS;
                //主表
                foreach (var c in tableColumns)
                {
                    if (!resultColumn.Include(c.PropertyName))
                        continue;

                    string cname = tableNameAlias + "." + c.Name;
                    string calias = tableNameAlias + "_" + c.Name;
                    builderSelect.Append(cname + " AS " + calias);
                    builderSelect.Append(",");
                }

                //关联表
                foreach (var propertyName in dicBelongs.Keys)
                {
                    //需要忽略的关联属性
                    if (!resultColumn.Include(propertyName))
                        continue;

                    //SELECT
                    BelongsInfo bc = dicBelongs[propertyName];

                    int cascade = propertyName.Split('.').Length;
                    List<ColumnInfo> columns = DbMapper.GetColumns(bc.Type, cascade); //获取关联实体 数据库映射信息
                    foreach (var c in columns)
                    {
                        string belongsPropertyName = bc.PropertyName + "." + c.PropertyName;

                        if (!resultColumn.Include(belongsPropertyName))
                            continue;

                        //  t1.ID AS t1_ID , t1.Name AS t1_Name
                        string cname = bc.Alias + "." + c.Name;
                        string calias = bc.Alias + "_" + c.Name;
                        builderSelect.Append(cname + " AS " + calias);
                        builderSelect.Append(",");
                    }
                }
                #endregion
            }
            else
            {
                //主表

                if (resultColumn.IsEmpty)
                {
                    builderSelect.Append("  *  ");
                }
                else
                {
                    List<ColumnInfo> tableColumns = DbMapper.GetColumns(typeof(T), 0);
                    foreach (var c in tableColumns)
                    {
                        if (!resultColumn.Include(c.PropertyName))
                            continue;

                        string cname = c.Name;
                        string calias = c.Name;
                        builderSelect.Append(cname + " AS " + calias);
                        builderSelect.Append(",");
                    }
                }
            }
            sql = builderSelect.Remove(builderSelect.Length - 1, 1).ToString();

            return sql;
        }

        /// <summary>
        /// 拼接数据表
        /// </summary>
        /// <param name="resultColumn">需要查询的结果属性集合</param>
        /// <returns></returns>
        public static string BuildFromSql<T>(ResultColumn resultColumn, SearchArgs searchArgs)
        {
            //当查询结果项不为空，且包含查询项时，把查询项，添加至查询列
            if (resultColumn != null && !resultColumn.IsEmpty &&
                searchArgs != null && searchArgs.Fields != null && searchArgs.Fields.Count > 0)
            {
                foreach (var propertyName in searchArgs.Fields)
                    resultColumn.Add(propertyName);
            }

            string sql = string.Empty;
            string tableName = DbMapper.GetTableName<T>();

            Dictionary<string, BelongsInfo> dicBelongs = DbMapper.GetBelongsColumnsDic<T>();
            if (dicBelongs != null && dicBelongs.Count > 0)
            {
                #region 有外键关联的情况

                string tableNameAlias = DbMapper.PRIMARY_TABLE_ALIAS;
                StringBuilder builderFrom = new StringBuilder();
                builderFrom.Append(tableName + " " + tableNameAlias);

                //关联表
                foreach (var belongsPropertyName in dicBelongs.Keys)
                {
                    if (resultColumn != null)
                    {
                        if (!resultColumn.Include(belongsPropertyName))
                            continue;
                    }

                    BelongsInfo bc = dicBelongs[belongsPropertyName];
                    string tname = DbMapper.GetTableName(bc.Type);

                    if (bc.JoinBehaviour == JoinBehaviour.Inner)
                        builderFrom.Append(" JOIN " + tname + " " + bc.Alias);
                    else
                        builderFrom.Append(" LEFT JOIN " + tname + " " + bc.Alias);

                    string belongsFK = bc.Name;
                    string belongsPK = DbMapper.GetPrimaryKey(bc.Type).Name;
                    string onAlias = !string.IsNullOrEmpty(bc.AliasReference) ? bc.AliasReference : tableNameAlias;

                    builderFrom.Append(" ON " + onAlias + "." + belongsFK + "=" + bc.Alias + "." + belongsPK);
                }
                sql = builderFrom.ToString();

                #endregion
            }
            else
            {
                sql = tableName;
            }
            return sql;
        }

        /// <summary>
        /// 获取数据库查询参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="field"></param>
        /// <param name="paramName"></param>
        /// <returns></returns>
        public static DataParameter GetDbDataParameter<T>(T obj, ColumnInfo field, string paramName)
        {
            DataParameter param = new DataParameter();
            param.ParameterName = paramName;
            param.Size = field.Size;
            object value = ReflectionHelper.GetPropertyValue<T>(obj, field.PropertyName);

            if (value is Enum)
            {
                value = (int)value;
                param.Value = value;
                param.DbType = DbType.Int32;
            }
            else if (value is Boolean)
            {
                param.Value = value;
                param.DbType = DbType.Boolean;
            }
            else if (value is DateTime)
            {
                param.Value = value;
                param.DbType = DbType.DateTime;
            }
            else
            {
                param.Value = value;
                if (value == null)
                    param.DbType = DbType.String;
                else
                    param.DbType = field.Type;
            }
            return param;
        }

    }
}
